package gui.dao;

import gui.entity.Config;
import gui.util.DButil;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

public class ConfigDAO {

    public int getTotal(){
    int total=0;
    try( Connection c = DButil.getConnection();
    Statement s=c.createStatement();){
            String sql="select count(*) from config";
        ResultSet rs = s.executeQuery(sql);
        while(rs.next()){
            total=rs.getInt(1);
        }
        } catch (Exception e) {
            e.printStackTrace();
        }
    return total;
    }
    public void add(Config config) throws Exception {
        String sql = "insert into config value (null,?,?)";
        try(Connection conn =DButil.getConnection();
        PreparedStatement ps =conn.prepareStatement(sql)){
            ps.setString(1,config.getKey());
            ps.setString(2,config.getValue());
            ps.execute();
            ResultSet rs =ps.getGeneratedKeys();
            if (rs.next()) {
                int id = rs.getInt(1);
                config.setId(id);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
            }
    public void update(Config config) throws Exception {

        String sql = "update config set key_= ?, value=? where id = ?";
        try (Connection c = DButil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setString(1, config.getKey());
            ps.setString(2, config.getValue());
            ps.setInt(3, config.getId());

            ps.execute();

        } catch (SQLException e) {

            e.printStackTrace();
        }

    }

    public void delete(int id) throws Exception {

        try (Connection c = DButil.getConnection(); Statement s = c.createStatement();) {

            String sql = "delete from config where id = " + id;

            s.execute(sql);

        } catch (SQLException e) {

            e.printStackTrace();
        }
    }

    public Config get(int id) throws Exception {
        Config config = null;

        try (Connection c = DButil.getConnection(); Statement s = c.createStatement();) {

            String sql = "select * from config where id = " + id;

            ResultSet rs = s.executeQuery(sql);

            if (rs.next()) {
                config = new Config();
                String key = rs.getString("key_");
                String value = rs.getString("value");
                config.setKey(key);
                config.setValue(value);
                config.setId(id);
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return config;
    }
    //查询所有
    public List<Config> list() throws Exception {
        return list(0, Short.MAX_VALUE);
    }
    //分页查询
    public List<Config> list(int start, int count) throws Exception {
        List<Config> configs = new ArrayList<Config>();

        String sql = "select * from config order by id desc limit ?,? ";

        try (Connection c = DButil.getConnection(); PreparedStatement ps = c.prepareStatement(sql);) {

            ps.setInt(1, start);
            ps.setInt(2, count);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                Config config = new Config();
                int id = rs.getInt(1);
                String key = rs.getString("key_");
                String value = rs.getString("value");
                config.setId(id);
                config.setKey(key);
                config.setValue(value);
                configs.add(config);
            }
        } catch (SQLException e) {

            e.printStackTrace();
        }
        return configs;
    }

    public Config getByKey(String key) throws Exception {
        Config config = null;
        String sql = "select * from config where key_ = ?" ;
        try (Connection c = DButil.getConnection();
             PreparedStatement ps = c.prepareStatement(sql);
        ) {

            ps.setString(1, key);
            ResultSet rs =ps.executeQuery();

            if (rs.next()) {
                config = new Config();
                int id = rs.getInt("id");
                String value = rs.getString("value");
                config.setValue(value);
                config.setKey(key);
                config.setId(id);
            }

        } catch (SQLException e) {

            e.printStackTrace();
        }
        return config;
    }



}
